﻿using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using OrionApplication.OrionLib.Orion;

namespace OrionApplication.OrionLib.Dao
{
    public class DaoBase : Interface.DaoInterface
    {
        /// <summary>
        /// Ensemble de paramétre MySQL des requêtes
        /// </summary>
        protected List<MySqlParameter> lParam = new List<MySqlParameter>();
        /// <summary>
        /// Nom de la table associée à l'élément en cours
        /// </summary>
        protected string TableName = string.Empty;
        /// <summary>
        /// Nom de la vue associée à l'élément en cours
        /// </summary>
        protected string ViewName = string.Empty;
        /// <summary>
        /// Etabli le SELECT complet (juste avant le FROM) du composant en cours
        /// </summary>
        protected string DebutSelect
        {
            get
            {
                string retour = "SELECT ";
                // On obtient les colonnes sous forme de liste
                List<string> ls = this.GetColumnName();
                // Initialisation du compteur
                int i = 1;
                // On parcourt toute la liste
                foreach (string s in ls)
                {
                    // On construit la liste des colonnes
                    retour += s;
                    // Si dernière colonne, on ne précise pas la virgule
                    if (i < ls.Count)
                        retour += ",";
                    i++;
                }
                retour += " FROM ";
                return retour;
            }
        }
        public long LastInsertId;

        /// <summary>
        /// Obtient les noms de colonnes de la table MySQL 
        /// </summary>
        /// <returns>Liste des noms de colonnes</returns>
        protected List<string> GetColumnName()
        {
            // Liste des colonnes à retourner
            List<string> retour = new List<string>();

            // Préparation de la requête
            String req = "DESCRIBE " + this.TableName;

            // Execution de la requête
            MySqlDataReader dr = this.ExecuteQueryReader(req);

            // On parcourt les différents résultats
            while (dr.Read())
                retour.Add(dr.GetString("Field"));
            dr.Close();

            return retour;
        }

        /// <summary>
        /// Supprimme l'enregistrement de la table du DAO 
        /// </summary>
        /// <param name="OrionId">Identifiant unique de la table</param>
        public void Delete(Int32 OrionId)
        {
            // Préparation de la requête
            String req = "DELETE FROM " + this.TableName + " WHERE OrionId=@orionid";
            // On nettoie les paramétres
            lParam.Clear();
            // On ajoute le paramétre à la liste
            lParam.Add(new MySqlParameter("@orionid", OrionId));
            // On exécute la requête avec la liste des paramétres
            this.ExecuteNonQuery(req, lParam);
        }

        /// <summary>
        /// Permet de mettre à jour l'OrionEtat de l'enregistrement
        /// </summary>
        /// <param name="OrionId">Identifiant Unique de l'enregistrement</param>
        /// <param name="OrionEtat">Nouvel état à mettre à jour</param>
        public void UpdateOrionEtat(Int32 OrionId, Boolean OrionEtat)
        {
            lParam.Clear();
            String req = "UPDATE " + this.TableName + " SET OrionEtat=@orionetat, OrionMaj=@orionmaj WHERE OrionId=@orionid";
            lParam.Add(new MySqlParameter("@orionid", OrionId));
            lParam.Add(new MySqlParameter("@orionetat", OrionEtat));
            lParam.Add(new MySqlParameter("@orionmaj", DateTime.Now));
            this.ExecuteNonQuery(req, lParam);
        }

        /// <summary>
        /// Renvoie le contenu complet de la table dans un DataSet
        /// </summary>
        /// <returns>DataSet : Vu compplète de la table</returns>
        public DataSet SelectAllDS()
        {
            lParam.Clear();
            DataSet retour = new DataSet();
            if (this.TableName != string.Empty)
            {
                String req = this.DebutSelect
                    + this.ViewName;

                retour = this.ExecuteQueryDataSet(req);
            }
            return retour;
        }

        /// <summary>
        /// Renvoie le contenu complet de la Vue dans un DataSet
        /// </summary>
        /// <returns>DataSet : Vu compplète de la table</returns>
        public DataSet SelectAllDSView()
        {
            lParam.Clear();
            DataSet retour = new DataSet();
            if (this.TableName != string.Empty)
            {
                String req = "SELECT * FROM "
                    + this.ViewName;

                retour = this.ExecuteQueryDataSet(req);
            }
            return retour;
        }
        
        /// <summary>
        /// Renvoie le contenu complet de la Vue dans un DataSet
        /// </summary>
        /// <returns>DataSet : Vu compplète de la table</returns>
        public DataSet SelectFromRequete(string req)
        {
            lParam.Clear();
            DataSet retour = new DataSet();
            retour = this.ExecuteQueryDataSet(req);
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <param name="lParam"></param>
        /// <returns></returns>
        protected Int32 ExecuteNonQuery(String req, List<MySqlParameter> lParam)
        {
            if (!req.Contains("OrionLog"))
                OrionOutilsBase.Log(req, lParam);

            Int32 retour = 0;
            try
            {
                MySqlCommand cmd = new MySqlCommand(req, OrionConfBase.GetConnexion());
                foreach (MySqlParameter param in lParam)
                    cmd.Parameters.Add(param);
                cmd.Prepare();
                retour = cmd.ExecuteNonQuery();
                if (req.StartsWith("INSERT"))
                    this.LastInsertId = cmd.LastInsertedId;
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        protected Int32 ExecuteNonQuery(String req)
        {
            if (!req.Contains("OrionLog"))
                OrionOutilsBase.Log(req);

            Int32 retour = 0;
            MySqlConnection conn = OrionConfBase.GetConnexion();
            try
            {
                MySqlCommand cmd = new MySqlCommand(req, conn);
                retour = cmd.ExecuteNonQuery();
                if (req.Contains("INSERT"))
                    this.LastInsertId = cmd.LastInsertedId;
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        public MySqlDataReader ExecuteQueryReader(String req)
        {
            MySqlDataReader retour = null;
            try
            {
                MySqlCommand cmd = new MySqlCommand(req, OrionConfBase.GetConnexion());
                retour = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <param name="lParam"></param>
        /// <returns></returns>
        public MySqlDataReader ExecuteQueryReader(String req, List<MySqlParameter> lParam)
        {
            MySqlDataReader retour = null;
            try
            {
                MySqlCommand cmd = new MySqlCommand(req, OrionConfBase.GetConnexion());
                foreach (MySqlParameter param in lParam)
                    cmd.Parameters.Add(param);
                cmd.Prepare();
                retour = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        protected DataSet ExecuteQueryDataSet(String req)
        {
            DataSet retour = new DataSet();
            MySqlDataAdapter mda;
            try
            {
                mda = new MySqlDataAdapter(req, OrionConfBase.GetConnexion());
                MySqlCommandBuilder cb = new MySqlCommandBuilder(mda);
                mda.Fill(retour);
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        protected DataSet ExecuteQueryDataSet(String req, List<MySqlParameter> lParam)
        {
            DataSet retour = new DataSet();
            MySqlDataAdapter mda;
            try
            {
                mda = new MySqlDataAdapter(req, OrionConfBase.GetConnexion());
                foreach (MySqlParameter param in lParam)
                    mda.SelectCommand.Parameters.Add(param);
                mda.SelectCommand.Prepare();
                mda.Fill(retour);
            }
            catch (Exception ex)
            {
                if(ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }
    }
}
